package o2o.task.postman;

import com.jinyou.utils.common.DateUtils;
import com.jinyou.utils.common.ValidateUtil;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.order.ORDER_STATUS;
import o2o.dao.PO.postman.PostManOrderStatistics;
import o2o.dao.VO.order.REFUND_APPLY_STATUS;
import o2o.system.data.SYS_SETTINGS;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;
import java.util.List;

/**
 * @Classname PostmanReleaseUtils
 * @Description TODO
 * @Date 2019/5/31 18:17
 * @Created by yangtk
 */
// 配送员相关统计
public class PostmanReleaseMan {

    // 配送员每日单量统计
    public static void postmanOrderDailyReleaseTask() {
        Date date = new Date(); // 当前时间
        Long startTime = DateUtils.getStartTime(date, -1); // 一天前的开始时间戳
        Long endTime = DateUtils.getEndTime(date, -1); //  一天前的结束时间戳
        Long localDayTime = startTime + 1 * 60 * 60 * 1000L; // 用当天的一点表示当前的时间
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ")
                .append("   CP.username AS username, ")
                .append("   IFNULL(II.counts ,0) AS  orderCounts, ")
                .append("   IFNULL(II.postmanMoney,0) AS totalMoney ")
                .append(" FROM   ")
                .append("    city_postman  AS CP ")
                .append(" LEFT JOIN ")
                .append("   (SELECT ")
                .append("       OI.postmanUsername, ")
                .append("       COUNT(OI.`orderNo`) AS counts,")
                .append("       SUM(IFNULL(OP.`postmanMoney`, 0.0)) AS postmanMoney ")
                .append("     FROM  ")
                .append("        order_info AS OI ")
                .append("     LEFT JOIN order_pay AS OP ")
                .append("     ON OI.`orderNo` = OP.`orderNo` AND OP.delFlag = 0  ")
                .append("     WHERE OI.`isZiQu` = 0  ");

        if (SYS_SETTINGS.POSTING_CANCEL_STATISTICS_FINISH == 0) {
            sql.append("     AND ((OI.`orderStatus` =  ").append(ORDER_STATUS.OVER)
                    .append("     AND OI.isRefundApply !=  ").append(REFUND_APPLY_STATUS.PASS).append(")")
                    .append(" or (")
                    .append(" (OI.orderStatus= ").append(ORDER_STATUS.CANCEL_BY_SYSTEM)
                    .append(" or OI.orderStatus= ").append(ORDER_STATUS.CANCEL)
                    .append(" or OI.orderStatus= ").append(ORDER_STATUS.PAYED_CANCEL)
                    .append(") and OI.isPostingCancel = 1")
                    .append(" )) ");
        } else {
            sql.append("     AND OI.`orderStatus` = ").append(ORDER_STATUS.OVER)
                    .append("     AND OI.isRefundApply != ").append(REFUND_APPLY_STATUS.PASS);
        }
        sql.append("     AND OI.`shopSelfPost` = 0  ")
                .append("     AND OI.delFlag = 0  ")
                .append("      ")
//                .append("     AND OI.ispaid = 1  ")
                .append("     AND OI.userFinishTime >=  ").append(startTime)
                .append("     AND OI.userFinishTime <  ").append(endTime)
                .append("     GROUP BY OI.postmanUsername) AS II  ")
                .append(" ON CP.username = II.postmanUsername ")
                .append(" WHERE delFlag = 0  ");

        List<PostManOrderStatistics> list = null;
        try {
            list = (List<PostManOrderStatistics>) UserDaoSql.getInstance().list(PostManOrderStatistics.class, sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("配送员单量统计失败");
        }
        String sameDayTime = DateUtils.sdfDate.get().format(new Date(localDayTime));

        if (ValidateUtil.isAbsList(list)) {
            try {
                insertPostmanOrders(list, localDayTime, DateUtils.getCurrTime(), startTime, endTime, sameDayTime);
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("骑手单量统计失败");
            }
        }
//        SELECT
//        CP.username AS username,
//                IFNULL(II.counts ,0) AS  counts,
//        IFNULL(II.postmanMoney,0) AS postmanMoney
//        FROM
//        city_postman  AS CP
//        LEFT JOIN
//        (SELECT
//        OI.postmanUsername,
//                COUNT(OI.`orderNo`) AS counts,
//        SUM(IFNULL(OP.`postmanMoney`, 0.0)) AS postmanMoney
//        FROM
//        order_info AS OI
//        LEFT JOIN order_pay AS OP
//        ON OI.`orderNo` = OP.`orderNo`
//        WHERE OI.`isZiQu` = 0
//        AND OI.`orderStatus` = 9
//        AND OI.`shopSelfPost` = 0
//        AND OI.delFlag = 0
//        AND OP.delFlag = 0
//        AND OI.ispaid = 1
//        AND OI.isRefundApply != 2
//        AND OI.userFinishTime > 0
//        AND OI.userFinishTime < 1559296229000
//        GROUP BY OI.postmanUsername) AS II
//        ON CP.username = II.postmanUsername
//        WHERE delFlag = 0

    }


    // 添加每日数据
    public static void insertPostmanOrders(List<PostManOrderStatistics> list, Long localDayTime, Long cTime, Long startTime, Long endTime, String simpleDateTime) throws Exception {
        if (!ValidateUtil.isAbsList(list)) {
            throw new Exception("list没有数据");
        }
        Connection conn = null;
        try {
            conn = UserDaoSql.getInstance().getConnection();
            conn.setAutoCommit(false);
            StringBuffer infoSb = new StringBuffer(" INSERT INTO ").append(PojoTables.POSTMAN_ORDER_DAILY_STATISTICS)
                    .append(" (username,orderCounts,totalMoney,localDayDate,")   // 4
                    .append("localDayTime,startTime,endTime,delFlag,createUser,")  //5
                    .append("createTime)") //2
                    .append("values(?,?,?,?,")
                    .append("?,?,?,?,?,")
                    .append("?)");
            PreparedStatement infoPre = null;
            try {
                infoPre = conn.prepareStatement(infoSb.toString());
            } catch (Exception e) {
                e.printStackTrace();
                UserDaoSql.getInstance().rollback(conn);
            }
            for (PostManOrderStatistics info : list) {
                infoPre.setString(1, info.getUsername());
                infoPre.setInt(2, info.getOrderCounts());
                infoPre.setDouble(3, info.getTotalMoney());
                infoPre.setString(4, simpleDateTime);
                infoPre.setLong(5, localDayTime);
                infoPre.setLong(6, startTime);
                infoPre.setLong(7, endTime);
                infoPre.setInt(8, 0);
                infoPre.setString(9, "admin");
                infoPre.setLong(10, cTime);
                infoPre.addBatch();
            }
            // 添加
            infoPre.executeBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            UserDaoSql.getInstance().rollback(conn);
            throw e;
        } finally {
            UserDaoSql.getInstance().close(conn);
        }
    }

}